﻿Imports System.Data
Imports System.Data.SqlClient

Partial Class driver_MasterPage
    Inherits System.Web.UI.MasterPage

    Dim connString As String = "Data Source=sql2008.campus.jcu.edu ; Initial Catalog=service; User Id=Thunder48; Password=Thundertech169;"
    Private _dataSet As IAsyncResult

    Private Property DataSet As IAsyncResult
        Get
            Return _dataSet
        End Get
        Set(value As IAsyncResult)
            _dataSet = value
        End Set
    End Property

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Session("loggedin") = False Then
            Session("referer") = Request.RawUrl 'Store so you can go to the page once logged in
            Response.Redirect("default.aspx")
        End If

        'Response.Write(Session.SessionID.ToString())
    End Sub

    Public Sub bindListBox(ByRef lb As ListBox, table As String, column As String)
        Dim conn As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim sql As String

        sql = "select * from " & table & " where semester_yearid='" & getSemester() & "'"
        'Response.Write(sql)
        conn = New SqlConnection(connString)

        Try
            conn.Open()
            command = New SqlCommand(sql, conn)
            adapter.SelectCommand = command
            adapter.Fill(ds)
            adapter.Dispose()
            command.Dispose()
            conn.Close()


            If ds.Tables(0).Rows.Count > 0 Then
                lb.DataSource = ds
                lb.DataTextField = column
                lb.DataValueField = column
                lb.DataBind()
            End If

        Catch ex As Exception
            'Response.Write(ex)
        End Try
    End Sub

    Public Sub bindDropDownList(ByRef ddl As DropDownList, table As String, column As String)
        Dim conn As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim sql As String

        sql = "select * from " & table & " where semester_yearid='" & getSemester() & "'"

        conn = New SqlConnection(connString)

        Try
            conn.Open()
            command = New SqlCommand(sql, conn)
            adapter.SelectCommand = command
            adapter.Fill(ds)
            adapter.Dispose()
            command.Dispose()
            conn.Close()

            ddl.DataSource = ds.Tables(0)

            If ds.Tables(0).Rows.Count > 0 Then
                ddl.DataSource = ds
                ddl.DataTextField = column
                ddl.DataValueField = column
                ddl.DataBind()
            End If

        Catch ex As Exception
            'Response.Write(ex)
        End Try
    End Sub

    Public Sub bindPreferenceListBox(ByRef lb As ListBox, table As String, column As String)
        Dim conn As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim sql As String

        sql = "select * from " & table & " where semester_yearid='" & getSemester() & "' and needed <> 'One-time project'"
        'Response.Write(sql)
        conn = New SqlConnection(connString)

        Try
            conn.Open()
            command = New SqlCommand(sql, conn)
            adapter.SelectCommand = command
            adapter.Fill(ds)
            adapter.Dispose()
            command.Dispose()
            conn.Close()


            If ds.Tables(0).Rows.Count > 0 Then
                lb.DataSource = ds
                lb.DataTextField = column
                lb.DataValueField = column
                lb.DataBind()
            End If

        Catch ex As Exception
            'Response.Write(ex)
        End Try
    End Sub

    Public Sub bindPreferenceLabel(ByRef lb As ListBox, ByRef lbl As Label)

        Dim conn As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim sql As String
        Dim reader As SqlDataReader

        'sql = "select [activityid],[description] from comserv_activity where [activity-name]='" & lb.SelectedValue.ToString.Trim() & "'"
        '1,2,3,4,5,6,7,8,9,10,11,12,13
        sql = "select [description],[begin_date],[end_date],[begin_time],[end_time],[travel_time],[sun],[mon],[tue],[wed],[thurs],[fri],[sat] from comserv_activity where [activity-name]='" & lb.SelectedValue.ToString.Trim() & "'"
        conn = New SqlConnection(connString)
        command = New SqlCommand(sql, conn)

        command.CommandText = sql
        command.Connection = conn

        Try
            conn.Open()
            reader = command.ExecuteReader()
            If reader.Read() Then
                lbl.Text = ""
                'lbl.Text = "ActivityID: " & reader.GetValue(0)
                'lbl.Text += vbCrLf
                'lbl.Text += reader.GetString(1)
                lbl.Text += "Begin Date: " & reader.GetDateTime(1) & "<br/>"
                lbl.Text += "End Date: " & reader.GetDateTime(2) & "<br/>"
                lbl.Text += "Begin Time: " & reader.GetDateTime(3) & "<br/>"
                lbl.Text += "End Time: " & reader.GetDateTime(4) & "<br/>"
                lbl.Text += "Drive Time: " & reader.GetValue(5) & "<br/>"
                lbl.Text += "Days of the Week: "
                'we check the days of the week :(
                'lbl.Text += "Days of the Week: " & reader.GetValue(6) & "<br/>" & reader.GetValue(7) & "<br/>" & reader.GetValue(8) & "<br/>" & reader.GetValue(9) & "<br/>" & reader.GetValue(10) & "<br/>" & reader.GetValue(11) & "<br/>" & reader.GetValue(12) & "<br/>" & reader.GetValue(13) & "<br/>"

                If reader.GetBoolean(6) = True Then
                    lbl.Text += " Sunday "
                ElseIf reader.GetBoolean(7) = True Then
                    lbl.Text += " Monday "
                ElseIf reader.GetBoolean(8) = True Then
                    lbl.Text += " Tuesday "
                ElseIf reader.GetBoolean(9) = True Then
                    lbl.Text += " Wednesday "
                ElseIf reader.GetBoolean(10) = True Then
                    lbl.Text += " Thursday "
                ElseIf reader.GetBoolean(11) = True Then
                    lbl.Text += " Friday "
                ElseIf reader.GetBoolean(12) = True Then
                    lbl.Text += " Saturday "
                End If

                lbl.Text += "<br/><br/>" & reader.GetString(0)
            End If
            conn.Close()
        Catch ex As Exception
            'lbl.Text = ""
            'Response.Write(ex)
        End Try

    End Sub

    Public Sub savePreferences(ByRef preference1 As ListBox, ByRef preference2 As ListBox, ByRef preference3 As ListBox)

        'we build the sql query - get activity id's from activity table and insert / update driver preferences
        'select activityid from comserv_activity where [activity-name]='testable' and semester_yearid='13'

        Dim conn As New SqlConnection
        Dim cmd As String
        Dim sql As SqlCommand = New SqlCommand
        conn.ConnectionString = connString
        conn.Open()
        Dim s1 As String = preference1.SelectedValue
        Dim s2 As String = preference2.SelectedValue
        Dim s3 As String = preference3.SelectedValue


        'if exists update
        Try
            cmd = "update driver_preferences set preference1='" & getActivityIdUsingName(s1) & "', preference2='" & getActivityIdUsingName(s2) & "', preference3='" & getActivityIdUsingName(s3) & "' where driverID='" & Session("driver_id") & "'"
            'cmd = "update driver_preferences set " & dropDown.ID & "='" & getActivityIdUsingName(dropDown) & "' where driverID='" & Session("driver_id") & "'"
            sql.CommandText = cmd
            'Response.Write(cmd)
            sql.Connection = conn
            sql.ExecuteNonQuery()

        Catch ex As Exception
            'Response.Write(ex)
        End Try

        'if it doesn't exist then create
        Try
            'cmd = "update driver_preferences set preference1='" &  & "', preference2='" &  & "', preference3='" &  & "' where driverID='" & Session("driver_id") & "'"
            cmd = "INSERT INTO driver_preferences (driverID, preference1, preference2, preference3) VALUES ("
            cmd += Session("driver_id") & ","
            cmd += getActivityIdUsingName(s1) & ","
            cmd += getActivityIdUsingName(s2) & ","
            cmd += getActivityIdUsingName(s3) & ")"
            'Response.Write(cmd)
            sql.CommandText = cmd
            sql.Connection = conn
            sql.ExecuteNonQuery()

        Catch ex As Exception
            'Response.Write(ex)
        End Try

        conn.Close()
        'Response.Redirect("portal.aspx")

    End Sub

    Public Function getActivityIdUsingName(ByVal activityName As String) As String

        Dim activityId As Integer = 0

        Dim conn As New SqlConnection
        Dim cmd As String
        Dim sql As SqlCommand = New SqlCommand
        Dim reader As SqlDataReader
        conn.ConnectionString = connString
        conn.Open()
        'cmd = "select activityid from comserv_activity where [activity-name]='" & dropDown.SelectedValue & "' and semester_yearid='" & Session("semester") & "'"
        cmd = "select activityid from comserv_activity where [activity-name]='" & activityName & "'"
        sql.CommandText = cmd
        sql.Connection = conn
        reader = sql.ExecuteReader()

        Try
            If reader.HasRows Then
                reader.Read()
                activityId = reader.GetValue(0)
            Else
                activityId = 0
            End If
        Catch ex As Exception
            'Context.Response.Write(ex)
        End Try

        conn.Close()

        Return activityId

    End Function

    Public Function getSemester() As Integer

        Dim semester_yearid As Integer

        Dim conn As New SqlConnection
        Dim cmd As String
        Dim sql As SqlCommand = New SqlCommand
        Dim reader As SqlDataReader
        conn.ConnectionString = connString
        conn.Open()
        cmd = "select top (1) semester_yearid from COMSERV_SEMESTER_YEAR order by semester_yearid desc"
        sql.CommandText = cmd
        sql.Connection = conn
        reader = sql.ExecuteReader()

        If reader.HasRows Then
            reader.Read()
            semester_yearid = reader.GetValue(0)
        Else
            semester_yearid = 0
        End If

        conn.Close()
        Return semester_yearid

    End Function

End Class

